﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ShopInTheEvent
{
    class DataHelper
    {
        public MySqlConnection connection;

        //construtor
        public DataHelper() 
        {
            String connectionInfo = "server=athena01.fhict.local;" +
                "database=dbi301970;" + "user id=dbi301970;" + "password=RtNR7HGBTZ;" + "connect timeout=30;";
            connection = new MySqlConnection(connectionInfo);
        }

        //method:GetAllProductInfo
        public List<ProductInfo> GetAllProductInfo() 
        {
            string sql = "SELECT * FROM product_info";
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<ProductInfo> temp = new List<ProductInfo>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string product_code, product_name;
                double price;
                int in_stock;

                while (reader.Read())
                {
                    product_code = Convert.ToString(reader["product_code"]);
                    product_name = Convert.ToString(reader["product_name"]);
                    price = Convert.ToDouble(reader["price"]);
                    in_stock = Convert.ToInt32(reader["in_stock"]);
                    temp.Add(new ProductInfo(product_code, product_name, price, in_stock));
                }
            }
            catch
            {
                MessageBox.Show("error while loading product Info!");
            }
            finally 
            {
                connection.Close();
            }
            return temp;
        }

        //method:Search
        public List<ProductInfo> FindTheProduct(string product_code) 
        {
            string sql = "SELECT * FROM product_info WHERE product_code="+product_code;
            MySqlCommand command = new MySqlCommand(sql, connection);

            List<ProductInfo> temp = new List<ProductInfo>();

            try
            {
                connection.Open();
                MySqlDataReader reader = command.ExecuteReader();

                string fore_code, product_name;
                double price;
                int in_stock;

                while (reader.Read())
                {
                    fore_code = Convert.ToString(reader["product_code"]);
                    product_name = Convert.ToString(reader["product_name"]);
                    price = Convert.ToDouble(reader["price"]);
                    in_stock = Convert.ToInt32(reader["in_stock"]);
                    temp.Add(new ProductInfo(fore_code, product_name, price,in_stock));
                }
            }
            catch
            {
                MessageBox.Show("Cannot find the product!");
            }
            finally
            {
                connection.Close();
            }
            return temp;
        }

        //method:Add a totally new product
        public int AddNewProduct(string ProductCode, string name, double price,int in_stock) 
        {
            string sql = "INSERT INTO product_info VALUES('" + ProductCode + "','" + name + "'," + price + "," + in_stock + ")";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1;
            }
            finally 
            {
                connection.Close();
            }

        }

        //method:Update stock
        public int UpdateStock(string ProductCode,int in_stock)
        {
            string sql = "UPDATE product_info SET in_stock=in_stock+" + in_stock + " WHERE product_code='" + ProductCode + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1;
            }
            finally
            {
                connection.Close();
            }

        }

        //method: Remove a product from database
        public int DeleteTheProduct(string ProductCode) 
        {
            string sql = "DELETE FROM product_info WHERE product_code='"+ProductCode+"'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1;
            }
            finally
            {
                connection.Close();
            }
        }

        //method: Change price of product
        public int ChangePrice(string ProductCode,double price) 
        {
            string sql = "UPDATE product_info SET price=" + price + " WHERE product_code='" + ProductCode + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);

            try
            {
                connection.Open();
                int nrOfRecordsChanged = command.ExecuteNonQuery();
                return nrOfRecordsChanged;
            }
            catch
            {
                return -1;
            }
            finally
            {
                connection.Close();
            }
        }

    }
}
